package net.chenlin.dp.common.utils;

import net.chenlin.dp.modules.business.entity.po.DeviceCalibrationDataPo;
import net.chenlin.dp.modules.business.entity.po.ExcelmportDataPo;
import net.chenlin.dp.modules.business.entity.po.TerminalElectricEnergyDataPo;
import net.chenlin.dp.modules.newBusiness.entity.po.DeviceUsersDataPo;
import net.chenlin.dp.modules.newBusiness.entity.po.NewExcelmportDataPo;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author Yangsm
 * @ClassName: ExcelImportAnalysisUtils
 * @Description: TODO
 * @date 2022年11月13日 17:03
 */
public class ExcelImportAnalysisUtils {

    /**
     * 解析文件中的数据
     */
    public static List<TerminalElectricEnergyDataPo> parseRowCell(String filename, InputStream is, Integer sheetNo) throws Exception {
        Map map = new HashMap<>();

        try {
            Workbook workbook = null;
            // 判断excel的后缀，不同的后缀用不同的对象去解析
            // xls是低版本的Excel文件
            if (filename.endsWith(".xls")) {
                workbook = new XSSFWorkbook(is);
            }
            // xlsx是高版本的Excel文件
            if (filename.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(is);
            }
            if (workbook == null) {
                throw new Exception();
            }

            Sheet firstSheet = workbook.getSheetAt(0);
            String electricTableNo = firstSheet.getRow(2).getCell(0).getStringCellValue().trim();

            // 取到excel 中的第一张工作表
            Sheet sheet = workbook.getSheetAt(sheetNo);
            if (sheet == null) {
                throw new Exception();
            }

            List<TerminalElectricEnergyDataPo> poList = new ArrayList<>();

            // 工作表中第一行是表头，不获取，从第二行开始获取
            for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
                // 获取到这一行的数据
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }

                String date = row.getCell(0).getStringCellValue().trim();
                String measurementStartTime = date + " 00:00:00";
                Date startDate = null;
                startDate = transferString2Date(measurementStartTime);
                Calendar calendar = Calendar.getInstance();

                for (int i = 3; i < 99; i++) {
                    if (StringUtils.isEmpty(row.getCell(3).getStringCellValue().trim())) {
                        continue;
                    }
                    String carSerialNo = "";
                    TerminalElectricEnergyDataPo po = new TerminalElectricEnergyDataPo();
                    po.setElectricTableNo(electricTableNo);
                    po.setMeasurementStartTime(startDate);
                    calendar.setTime(startDate);
                    calendar.add(Calendar.MINUTE, 15);
                    Date endDate = calendar.getTime();
                    po.setMeasurementEndTime(endDate);
                    startDate = endDate;

                    if (row.getCell(i) != null) {
                        carSerialNo = row.getCell(i).getStringCellValue().trim();
                        System.out.println(carSerialNo);
                        Double averagePowerValue = new Double(carSerialNo);
                        po.setAveragePowerValue(averagePowerValue);
                    }
                    poList.add(po);
                    //System.out.println(poList.size());
                    //System.out.println("每行当前列的数据 "+carSerialNo);
                }
            }

            System.out.println(poList.get(0));
            return poList;
        } catch (Exception e) {
            System.out.println(" === " + e);
            throw e;
        }
    }

    public static Date transferString2Date(String s) {
        Date date = new Date();
        try {
            date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(s);
        } catch (ParseException e) {
            //LOGGER.error("时间转换错误, string = {}", s, e);
        }
        return date;
    }

    public static List<ExcelmportDataPo> deviceExcelParseRowCell(String filename, InputStream inputStream, int sheetNo) throws Exception {

        Map map = new HashMap<>();

        try {
            Workbook workbook = null;
            // 判断excel的后缀，不同的后缀用不同的对象去解析
            // xls是低版本的Excel文件
            if (filename.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            }
            // xlsx是高版本的Excel文件
            if (filename.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
            if (workbook == null) {
                throw new Exception();
            }

            // 取到excel 中的第一张工作表
            Sheet sheet = workbook.getSheetAt(sheetNo);
            if (sheet == null) {
                throw new Exception();
            }

            List<ExcelmportDataPo> poList = new ArrayList<>();

            // 工作表中第一行是表头，不获取，从第二行开始获取
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                // 获取到这一行的数据
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }

                ExcelmportDataPo po = new ExcelmportDataPo();
                Cell cell = row.getCell(10);
                if (cell == null) {
                    break;
                }
                cell.setCellType(CellType.STRING);
                String cellValue = cell.getStringCellValue();
                po.setDeviceNo(cellValue);
                if (cellValue.length() > 4) {
                    String deviceNo = cellValue.substring(cellValue.length() - 4, cellValue.length());
                    po.setDeviceNo(deviceNo);
                }
                Cell cell1 = row.getCell(1);
                cell1.setCellType(CellType.STRING);
                po.setComponentType(cell1.getStringCellValue());
                Cell cell2 = row.getCell(2);
                cell2.setCellType(CellType.STRING);
                po.setUserName(cell2.getStringCellValue());
                Cell cell3 = row.getCell(3);
                cell3.setCellType(CellType.STRING);
                po.setUserNo(cell3.getStringCellValue());
                Cell cell4 = row.getCell(4);
                cell4.setCellType(CellType.STRING);
                po.setElectricityAddress(cell4.getStringCellValue());
                Cell cell5 = row.getCell(5);
                cell5.setCellType(CellType.STRING);
                po.setElectricityNo(cell5.getStringCellValue());
                Cell cell6 = row.getCell(6);
                cell6.setCellType(CellType.STRING);
                po.setElectricityNum(Double.valueOf(cell6.getStringCellValue()));
                Cell cell7 = row.getCell(7);
                cell7.setCellType(CellType.STRING);
                po.setStartingCode(Double.valueOf(cell7.getStringCellValue()));
                Cell cell8 = row.getCell(8);
                cell8.setCellType(CellType.STRING);
                po.setEndCode(Double.valueOf(cell8.getStringCellValue()));
                Cell cell9 = row.getCell(9);
                CellType cellTypeEnum = cell9.getCellTypeEnum();
                Date newWriteTime = new Date();
                if (cellTypeEnum.getCode() == 0) {
                    cell9.setCellType(CellType.NUMERIC);
                    Date writeTime = DateUtil.getJavaDate(cell9.getNumericCellValue());
                    Calendar calendar = Calendar.getInstance(); //创建Calendar 的实例
                    calendar.setTime(writeTime);
                    calendar.add(Calendar.DAY_OF_MONTH, -1); //当前时间减去一天
                    newWriteTime = calendar.getTime();
                    System.out.println(newWriteTime);
                }
                if (cellTypeEnum.getCode() == 1) {
                    String stringCellValue = cell9.getStringCellValue();
                    cell9.setCellType(CellType.STRING);
                    Date writeTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(stringCellValue);
                    Calendar calendar = Calendar.getInstance(); //创建Calendar 的实例
                    calendar.setTime(writeTime);
                    calendar.add(Calendar.DAY_OF_MONTH, -1); //当前时间减去一天
                    newWriteTime = calendar.getTime();
                    System.out.println(newWriteTime);
                }

                po.setWriteTime(newWriteTime);
//                Cell cell10 = row.getCell(10);
//                cell10.setCellType(CellType.STRING);
//                Date assertTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(cell10.getStringCellValue());
//                po.setAssertTime(assertTime);
                poList.add(po);

            }

            //System.out.println(poList.get(0));
            return poList;
        } catch (Exception e) {
            System.out.println(" === " + e);
            throw e;
        }
    }

    public static List<DeviceCalibrationDataPo> deviceCalibrationExcelParseRowCell(String filename, InputStream inputStream, int sheetNo) throws Exception {

        Map map = new HashMap<>();

        try {
            Workbook workbook = null;
            // 判断excel的后缀，不同的后缀用不同的对象去解析
            // xls是低版本的Excel文件
            if (filename.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            }
            // xlsx是高版本的Excel文件
            if (filename.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
            if (workbook == null) {
                throw new Exception();
            }

            // 取到excel 中的第一张工作表
            Sheet sheet = workbook.getSheetAt(sheetNo);
            if (sheet == null) {
                throw new Exception();
            }

            List<DeviceCalibrationDataPo> poList = new ArrayList<>();

            // 工作表中第一行是表头，不获取，从第二行开始获取
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                // 获取到这一行的数据
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }

                DeviceCalibrationDataPo po = new DeviceCalibrationDataPo();

                Cell cell1 = row.getCell(1);
                if (cell1 != null) {
                    cell1.setCellType(CellType.STRING);
                    if (StringUtils.isNotEmpty(cell1.getStringCellValue())) {
                        po.setDeviceBarCode(cell1.getStringCellValue());
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }

                Cell cell2 = row.getCell(2);
                cell2.setCellType(CellType.STRING);
                po.setDeviceNo(cell2.getStringCellValue());
                Cell cell3 = row.getCell(3);
                cell3.setCellType(CellType.STRING);
                po.setDeviceModel(cell3.getStringCellValue());
                Cell cell4 = row.getCell(4);
                cell4.setCellType(CellType.STRING);
                po.setDeviceRange(cell4.getStringCellValue());
                Cell cell5 = row.getCell(5);
                cell5.setCellType(CellType.STRING);
                po.setBelongingPlace(cell5.getStringCellValue());
                Cell cell6 = row.getCell(6);
                cell6.setCellType(CellType.STRING);
                po.setSimCode(cell6.getStringCellValue());
                Cell cell7 = row.getCell(7);
                cell7.setCellType(CellType.STRING);
                po.setProduceBatch(cell7.getStringCellValue());
                poList.add(po);

            }

            //System.out.println(poList.get(0));
            return poList;
        } catch (Exception e) {
            System.out.println(" === " + e);
            throw e;
        }
    }

    public static List<DeviceUsersDataPo> deviceUsersExcelParseRowCell(String filename, InputStream inputStream, int sheetNo) throws Exception {

        Map map = new HashMap<>();

        try {
            Workbook workbook = null;
            // 判断excel的后缀，不同的后缀用不同的对象去解析
            // xls是低版本的Excel文件
            if (filename.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            }
            // xlsx是高版本的Excel文件
            if (filename.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
            if (workbook == null) {
                throw new Exception();
            }

            // 取到excel 中的第一张工作表
            Sheet sheet = workbook.getSheetAt(sheetNo);
            if (sheet == null) {
                throw new Exception();
            }

            List<DeviceUsersDataPo> poList = new ArrayList<>();

            // 工作表中第一行是表头，不获取，从第二行开始获取
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                // 获取到这一行的数据
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }

                DeviceUsersDataPo po = new DeviceUsersDataPo();

                Cell cell1 = row.getCell(0);
                if (cell1 != null) {
                    cell1.setCellType(CellType.STRING);
                    if (StringUtils.isNotEmpty(cell1.getStringCellValue())) {
                        po.setUsername(cell1.getStringCellValue());
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
                Cell cell2 = row.getCell(1);
                if (cell2 != null) {
                    cell2.setCellType(CellType.STRING);
                    if (StringUtils.isNotEmpty(cell2.getStringCellValue())) {
                        po.setUserNo(cell2.getStringCellValue());
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
                Cell cell3 = row.getCell(2);
                if (cell3 != null) {
                    cell3.setCellType(CellType.STRING);
                    if (StringUtils.isNotEmpty(cell3.getStringCellValue())) {
                        po.setDeviceAlias(cell3.getStringCellValue());
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
                Cell cell4 = row.getCell(3);
                if (cell4 != null) {
                    cell4.setCellType(CellType.STRING);
                    if (StringUtils.isNotEmpty(cell4.getStringCellValue())) {
                        po.setConnection(cell4.getStringCellValue());
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
                Cell cell5 = row.getCell(4);
                if (cell5 != null) {
                    cell5.setCellType(CellType.STRING);
                    if (StringUtils.isNotEmpty(cell5.getStringCellValue())) {
                        po.setChangeRatio(cell5.getStringCellValue());
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
                poList.add(po);

            }

            //System.out.println(poList.get(0));
            return poList;
        } catch (Exception e) {
            System.out.println(" === " + e);
            throw e;
        }
    }

    public static List<NewExcelmportDataPo> userDataExcelParseRowCell(String filename, InputStream inputStream, int sheetNo) throws Exception {

        Map map = new HashMap<>();

        try {
            Workbook workbook = null;
            // 判断excel的后缀，不同的后缀用不同的对象去解析
            // xls是低版本的Excel文件
            if (filename.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            }
            // xlsx是高版本的Excel文件
            if (filename.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
            if (workbook == null) {
                throw new Exception();
            }

            // 取到excel 中的第一张工作表
            Sheet sheet = workbook.getSheetAt(sheetNo);
            if (sheet == null) {
                throw new Exception();
            }

            List<NewExcelmportDataPo> poList = new ArrayList<>();

            // 工作表中第一行是表头，不获取，从第二行开始获取
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                // 获取到这一行的数据
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }

                NewExcelmportDataPo po = new NewExcelmportDataPo();

                Cell cell1 = row.getCell(0);
                if (cell1 != null) {
                    cell1.setCellType(CellType.STRING);
                    if (StringUtils.isNotEmpty(cell1.getStringCellValue())) {
                        po.setUserName(cell1.getStringCellValue());
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
                Cell cell2 = row.getCell(1);
                if (cell2 != null) {
                    cell2.setCellType(CellType.STRING);
                    if (StringUtils.isNotEmpty(cell2.getStringCellValue())) {
                        po.setUserNo(cell2.getStringCellValue());
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
                Cell cell3 = row.getCell(2);
                CellType cellTypeEnum = cell3.getCellTypeEnum();
                if (cell3 != null) {

                    Date assertTime = new Date();
                    if (cellTypeEnum.getCode() == 0) {
                        cell3.setCellType(CellType.NUMERIC);
                        Date writeTime = DateUtil.getJavaDate(cell3.getNumericCellValue());
                        Calendar calendar = Calendar.getInstance(); //创建Calendar 的实例
                        calendar.setTime(writeTime);
                        calendar.add(Calendar.DAY_OF_MONTH, -1); //当前时间减去一天
                        assertTime = calendar.getTime();
                        System.out.println(assertTime);

                        po.setWriteTime(writeTime);
                        po.setAssertTime(assertTime);
                    }
                    if (cellTypeEnum.getCode() == 1) {
                        String stringCellValue = cell3.getStringCellValue();
                        cell3.setCellType(CellType.STRING);
                        Date writeTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(stringCellValue);
                        Calendar calendar = Calendar.getInstance(); //创建Calendar 的实例
                        calendar.setTime(writeTime);
                        calendar.add(Calendar.DAY_OF_MONTH, -1); //当前时间减去一天
                        assertTime = calendar.getTime();
                        System.out.println(assertTime);
                        po.setWriteTime(writeTime);
                        po.setAssertTime(assertTime);
                    }
                } else {
                    continue;
                }
                Cell cell4 = row.getCell(3);
                if (cell4 != null) {
                    cell4.setCellType(CellType.STRING);
                    if (StringUtils.isNotEmpty(cell4.getStringCellValue())) {
                        Double electricityNum = Double.valueOf(cell4.getStringCellValue());
                        po.setElectricityNum(electricityNum);
                    } else {
                        continue;
                    }
                } else {
                    continue;
                }
                poList.add(po);

            }

            //System.out.println(poList.get(0));
            return poList;
        } catch (Exception e) {
            System.out.println(" === " + e);
            throw e;
        }
    }
}
